﻿Imports System
Imports System.Data
Imports System.Data.Common
Imports System.Data.SQLite
Imports System.Collections.Generic

''' <summary>
''' SQLite数据库访问类
''' </summary>
''' <remarks>
''' 历史数据、历史报警、日志访问类
''' </remarks>
Public Class SqlHelper2

    Private Shared _ConnectionString As String

    ''' <summary>
    ''' 共享构造函数
    ''' </summary>
    ''' <remarks></remarks>
    Shared Sub New()
        '_ConnectionString = String.Format("Data Source={0}", Application.StartupPath & "\Projects\config.dat")
        '_ConnectionString = "Data Source=F:\config.dat"

        If My.Computer.Name = "RD221" Then
            _ConnectionString = "Data Source=F:\HData.dat"
        Else
            _ConnectionString = String.Format("Data Source={0}", Application.StartupPath & "\HData.dat")
        End If

    End Sub

    Private Shared Function GetConnection() As SQLiteConnection
        Dim cn As New SQLiteConnection(_ConnectionString)

        Try
            cn.Open()
            cn.Close()
        Catch ex As Exception
            Dim strMsg As String = "打开数据库连接失败"
            Dim ep As New Dictionary(Of String, Object)
            ep.Add("ConnectionString", _ConnectionString)
            Logger.WriteError(strMsg, ep)
        End Try

        Return cn
    End Function

    ''' <summary>
    ''' 执行选择操作
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteDataset(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As DataSet
        Dim ds As New DataSet

        'If commandParameters IsNot Nothing Then
        '    cmdText &= " test"
        'End If

        Try
            Dim cn As SQLiteConnection = GetConnection()
            Dim cmd As New SQLiteCommand(cn)

            cmd.CommandText = cmdText
            If commandParameters IsNot Nothing Then
                cmd.Parameters.AddRange(commandParameters)
            End If

            Dim da As New SQLiteDataAdapter(cmd)

            da.Fill(ds)

            da.Dispose()

        Catch ex As Exception
            Dim strMsg As String = "ExecuteDataset错误"
            Dim ep As New Dictionary(Of String, Object)
            ep.Add("commandText", cmdText)

            If commandParameters IsNot Nothing Then
                Dim i As Integer = 1
                For Each p As DbParameter In commandParameters
                    ep.Add("Parameter" & i.ToString(), p.Value)
                    i += 1
                Next
            End If
            Logger.WriteError(strMsg, ep)

            msg.log(ex.Message)

            'Return Nothing
        End Try

        Return ds
    End Function

    ''' <summary>
    ''' 执行更新操作
    ''' </summary>
    ''' <param name="cmdText"></param>
    ''' <param name="commandParameters"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Shared Function ExecuteNonQuery(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As Integer
        Dim cn As SQLiteConnection = GetConnection()
        Dim cmd As New SQLiteCommand(cn)

        cmd.CommandText = cmdText
        If commandParameters IsNot Nothing Then
            cmd.Parameters.AddRange(commandParameters)
        End If

        Dim result As Integer = 0

        Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            result = cmd.ExecuteNonQuery()
        Catch ex As Exception
            Dim strMsg As String = "ExecuteNonQuery()错误"
            Dim ep As New Dictionary(Of String, Object)
            ep.Add("commandText", cmdText)
            If commandParameters IsNot Nothing Then
                For Each p As DbParameter In commandParameters
                    ep.Add(p.ParameterName, p.Value)
                Next
            End If
            Logger.WriteError(strMsg, ep)

            result = -1
            msg.log(ex.Message)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If

            cn.Dispose()
        End Try

        Return result

    End Function

    Public Shared Function ExecuteScalar(ByVal cmdText As String, ByVal commandParameters As System.Data.Common.DbParameter()) As Object
        Dim cn As SQLiteConnection = GetConnection()
        Dim cmd As New SQLiteCommand(cn)

        cmd.CommandText = cmdText
        If commandParameters IsNot Nothing Then
            cmd.Parameters.AddRange(commandParameters)
        End If

        Dim obj As Object = Nothing

        Try
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
            obj = cmd.ExecuteScalar()
        Catch ex As Exception
            Dim strMsg As String = "ExecuteScalar()错误"
            Dim ep As New Dictionary(Of String, Object)
            ep.Add("commandText", cmdText)
            If commandParameters IsNot Nothing Then
                For Each p As DbParameter In commandParameters
                    ep.Add(p.ParameterName, p.Value)
                Next
            End If
            Logger.WriteError(strMsg, ep)

            obj = Nothing

            msg.log(ex.Message)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If

            cn.Dispose()
        End Try

        Return obj
    End Function

    Public Shared Function ExecuteTranstration(ByVal tic As TranstrationItem) As Boolean

        Dim cn As SQLiteConnection = GetConnection()
        Dim cmd As New SQLiteCommand(cn)

        Dim t As SQLiteTransaction = Nothing

        Try
            cn.Open()

            '开始事务
            t = cn.BeginTransaction()
            For Each item As KeyValuePair(Of String, System.Data.Common.DbParameter()) In tic
                cmd.CommandText = item.Key

                cmd.Parameters.Clear()
                If item.Value IsNot Nothing Then
                    cmd.Parameters.AddRange(item.Value)
                End If

                cmd.ExecuteNonQuery()
            Next

            '提交事务
            t.Commit()

        Catch ex As Exception
            Stop
            Try
                Dim strMsg As String = "ExecuteScalar()错误"
                Dim ep As New Dictionary(Of String, Object)
                For Each item As KeyValuePair(Of String, System.Data.Common.DbParameter()) In tic
                    ep.Add("commandText", item.Key)
                    If item.Value IsNot Nothing Then
                        For Each p As DbParameter In item.Value
                            ep.Add(p.ParameterName, p.Value)
                        Next
                    End If
                    ep.Add("-------", item)
                Next

                Logger.WriteError(strMsg, ep)

                msg.log(ex.Message)
            Catch ex2 As Exception
                'Do Nothing
            End Try


            Try
                If t IsNot Nothing Then
                    '回滚事务
                    t.Rollback()
                End If
            Catch ex3 As Exception
                Logger.WriteError("事务回滚失败" & ex.Message, Nothing)

                msg.log(ex3.Message)

                Return False
            End Try

            Return False
        Finally
            If (cn.State = ConnectionState.Open) Then
                cn.Close()
            End If

            cn.Dispose()
        End Try

        Return True

    End Function

End Class
 


